---
id: sql-schema
title: SQL Schema
slug: /atlas-schema/sql
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

Atlas supports defining database schemas in various ways, from its own [HCL language](hcl.mdx) to external ORMs,
programs, or the standard SQL. This guide focuses on defining schemas using SQL syntax, but also covers other methods.
There are multiple ways to define schemas in SQL, such as using a single schema SQL file, a directory, or
Go template-based directory. All of these methods are covered below.

## Dev Database

When working with SQL schemas, Atlas requires a URL to a [_Dev Database_](concepts/dev.mdx), specified via the
`--dev-url` flag (or the `dev` attribute in `atlas.hcl`). Typically, this is a temporary database running locally, used
to parse and validate the SQL definition. This requirement is necessary as Atlas cannot replicate every database type
'X' in every version 'Y'.

To simplify the process of creating temporary databases for one-time use, Atlas can spin up an ephemeral local Docker
container using the special [docker driver](concepts/url.mdx), and clean it up at the end of the process. Here are a
few examples of how to use the docker driver:

<Tabs>
<TabItem value="mysql" label="MySQL" default>

```shell
# When working on a single database schema.
--dev-url "docker://mysql/8/schema"

# When working on multiple database schemas.
--dev-url "docker://mysql/8"
```
</TabItem>
<TabItem value="mariadb" label="MariaDB">

```shell
# When working on a single database schema.
--dev-url "docker://maria/latest/schema"

# When working on multiple database schemas.
--dev-url "docker://maria/latest"
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
# When working on a single database schema, use the auth-created
# "public" schema as the search path.
--dev-url "docker://postgres/15/dev?search_path=public"

# When working on multiple database schemas.
--dev-url "docker://postgres/15/dev"
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
# Atlas supports working with an in-memory database in SQLite.
--dev-url "sqlite://dev?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
# When working on a single database schema.
--dev-url "docker://sqlserver/2022-latest/dev?mode=schema"

# When working on multiple database schemas.
--dev-url "docker://sqlserver/2022-latest/dev?mode=database"
```

</TabItem>

<TabItem value="clickhouse" label="ClickHouse">

```shell
# When working on a single database schema.
--dev-url "docker://clickhouse/23.11/schema"

# When working on multiple database schemas.
--dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>

## Schema Definition

Once the dev-database is set, Atlas utilizes it to convert the provided raw SQL files and statements into the Atlas
_"schema graph"_, that then can be used by various layers of the engine to _diff_, _plan_, and _apply_ changes onto the
target database. It's important to note that Atlas loads the raw SQL schema by executing the statements defined in the files
one by one. As such, it is expected that files and statements are ordered according to their dependencies. For example,
if a `VIEW` named `v1` depends on `TABLE` named `t1`, `v1` must be defined after `t1`, either in the same file or in a
separate one.

:::info Ensuring the dev-database is clean
As mentioned above, Atlas uses the dev database to compute the desired state of the database schema. Therefore, before
starting its work, Atlas ensures the dev database is clean and there are no leftovers from previous runs. Once done,
Atlas cleans up after itself and the dev database is ready for future runs.
:::

## Schema File

An SQL schema defined in a single file is typically named `schema.sql` and composed of multiple DDL statements separated
by a semicolon (`;`) or a [custom delimiter](../versioned/new.mdx#custom-statements-delimiter), which can be validly
executed onto a database one after the other.

```sql title="schema.sql"
-- create "users" table
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(100) NULL,
  PRIMARY KEY (`id`)
);

-- create "blog_posts" table
CREATE TABLE `blog_posts` (
  `id` int NOT NULL,
  `title` varchar(100) NULL,
  `body` text NULL,
  `author_id` int NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `example`.`users` (`id`)
);
```

In order to use an SQL schema file as an Atlas state, use the following format: `file://path/to/schema.sql`.

## Schema Directory

An SQL schema directory includes one or more schema files, ordered lexicographically according to their dependencies.
For example, a table with foreign keys must be defined after the other tables it references, and a view should also
be defined after the other tables and views it depends on.

In order to use a schema directory as an Atlas state, use the following format: `file://path/to/dir`.

## Template Directory

Atlas supports computing the desired schemas dynamically using [Go templates](https://pkg.go.dev/text/template) and
injected variables. To set it up for a project, create an `atlas.hcl` [config file](projects.mdx), if you don't already
have one. Then, declare a new data source of type [`template_dir`](projects.mdx#data-source-template_dir) that can be
used later as an Atlas schema.

```hcl title="atlas.hcl"
variable "path" {
  type        = string
  description = "A path to the template directory"
}

data "template_dir" "schema" {
  path = var.path
  vars = {
    key = "value"
    // Pass the --env value as a template variable.
    env  = atlas.env
  }
}

env "dev" {
  url = var.url
  src = data.template_dir.schema.url
}
```

```sql title="example/schema.tmpl.sql"
{{- if eq .env "dev" }}
    create table dev2 (c text);
    {{ template "shared/users" "dev2" }}
{{- else }}
    create table prod2 (c text);
    {{ template "shared/users" "prod2" }}
{{- end }}
```
